import openpyxl, time
from mysql import connector

# 下载excel
def downloadExcel(content_data_dict, header_data_dict):
    t1 = time.time()

    work = openpyxl.Workbook()
    # 激活当前页
    sheet = work.active

    # 设置头部
    header_ind = 0
    startInd = 65
    for key, values in header_data_dict.items():
        # print(key, values)
        posi = chr(startInd + header_ind) + repr(1)
        sheet[posi] = key
        header_ind += 1

    # 设置内容
    content_ind = 0
    for key, values in content_data_dict.items():
        # print(key, values)
        index = 2 + content_ind
        header_ind = 0
        for h_key, h_values in header_data_dict.items():
            posi = chr(startInd + header_ind) + repr(index)
            sheet[posi] = values[h_values]
            header_ind += 1
        content_ind += 1

    # 计算程序运行时间
    t2 = time.time()
    print(t2, t1, (t2 - t1))

    # 保存excel文件
    work.save('尹鹏孝.xlsx')


# 打开数据库连接
conn = connector.connect(
    host='127.0.0.1',  # 连接底座
    user='root',  # 数据库用户名
    password='123456',  # 用户名对应的密码
    database='python_mysql',  #
)
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = conn.cursor()
# 使用 execute() 方法执行 SQL
cursor.execute("select * from user limit 30000")
# 获取所有的结果
list = cursor.fetchall()
# 组合数据
dict_list = {}
for row in list:
    dict_list[list.index(row)] = {'name': row[0], 'phone': row[1], 'address': row[2]}

# 关闭数据库连接
conn.close()

# 下载excel
downloadExcel(dict_list,
              {'姓名': 'name', '电话': 'phone', '地址': 'address'})

'''
mywb = openpyxl.Workbook()

# 打印sheet页名称
print(mywb.get_sheet_names())

# 创建一个sheet页
# nsheet = mywb.create_sheet()
# print(nsheet.title)

# print(mywb.get_sheet_by_name(mywb))

# 激活
sheet = mywb.active

# 输出sheet页的索引，从0开始
# print(mywb.get_index(nsheet))

# 设置sheet的title
sheet.title = 'ds'

# 打印sheet页名称
print(mywb.get_sheet_names())

sheet['F6'] = 'dsfdsfs';

# 保存
mywb.save("1.xlsx")
'''